<?php
/**
 * Created by PhpStorm.
 * User: zyark
 * Date: 2018/3/21
 * Time: 14:32
 */

namespace app\common\utils;


use PHPExcel;
use PHPExcel_Cell;
use PHPExcel_IOFactory;
use PHPExcel_Shared_Date;
use think\Db;

class PHPExcelUtils
{


    public function read($filename, $encode = 'utf-8')
    {
        setlocale(LC_ALL, 'zh_CN');
        $objReader = PHPExcel_IOFactory::createReader('Excel5');//读取xls
        if (!$objReader->canRead($filename)) {
            //读取xlsx
            $objReader = PHPExcel_IOFactory::createReader('Excel2007');
            if (!$objReader->canRead($filename)) {
                return false;
            }
        }
        $objPHPExcel = $objReader->load($filename);
        $objWorksheet = $objPHPExcel->getActiveSheet();
        $highestRow = $objWorksheet->getHighestRow();
        $highestColumn = $objWorksheet->getHighestColumn();
        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
        $excelData = array();
        for ($row = 2; $row <= $highestRow; $row++) {
            $value = (string)$objWorksheet->getCellByColumnAndRow(0, $row)->getValue();
            if($value == ''){
                continue;
            }
            for ($col = 0; $col < $highestColumnIndex; $col++) {
                $value = (string)$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
                if($value == ''){
                    continue;
                }
//                if($col == 8){//指定H列为时间所在列
//                    $value = gmdate("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($value));
//                }
                if(!is_numeric($value) && $value != '') {
//                    $value = iconv('utf-8', 'gbk//ignore', $value);//解决中文乱码
                    $value = mb_convert_encoding($value,'utf-8');
                }
                $excelData[$row][] = $value;
            }
        }
        return $excelData;
    }


    public function setCellList($cellList){

        vendor('PHPExcel');
        $obj = new PHPExcel();
        $obj->setActiveSheetIndex(0);
        $objSheet = $obj->getActiveSheet();
        $len = count($cellList);
        $start = ord('A');
        for($i = 0; $i < $len; $i++)
        {
            $objSheet->setCellValue(chr($start).'1', $cellList[$i]);
            $start++;
        }

        return $obj;

    }

}